---语言帮助函数
local utilrds = {}

--======================================
--default params
local d = Ding
local Logger = d.Logger
--======================================
--[[ INSERT ]]
local TEMPLATE_INMSMERT_DATA = [[
INSERT INTO {tbname} ({defienks}) VALUES ({vals})
]]
--[[ SELECT ]]
local TEMPLATE_FIND_ONE = [[
SELECT * FROM `{tbname}` WHERE {condition}
]]
local TEMPLATE_FETCH_LIMITCNT = [[
SELECT * FROM ({tbname}) LIMIT {starnum},{endnum}
]]
local TEMPLATE_FINDINSET = [[
SELECT * FROM {tbname} WHERE FIND_IN_SET('{condkey}', '{condlist}');     
]]
local TEMPLATE_FINDIN = [[
SELECT * FROM {tbname} WHERE {condkey} IN ({condlist});     
]]
local TEMPLATE_FIND_GROUP_BY = [[
SELECT * FROM {tbname} GROUP BY {condlist};
]]
--[[ DELETE ]]
local TEMPLATE_DELETE_FROM = [[
DELETE FROM {tbname} WHERE {condition}
]]
--[[ UPDATE ]]
local TEMPLATE_UPDATE_TABLE = [[
UPDATE {tbname} SET {vallist} WHERE ({condition})
]]

--kv字典转换成为条件语句
local function _crt_kv_condition_str(conddic, isformula)
    local str_condi = ''

    local act_val
    local idx = 1
    local condlen = table.nums(conddic)
    local exstr = ''
    for idxk, val in pairs(conddic) do
        exstr = idx ~= condlen and ' AND ' or ''
        act_val = type(val) == 'string' and string.format(not isformula and "'%s'" or '%s', val) or val
        str_condi = str_condi .. string.format('%s=%s', idxk, act_val) .. exstr
        idx = idx + 1
    end
    return str_condi
end

--list转换为条件语句
local function _crt_list_condition_str(vlist)
    local llen = #vlist
    local ret = ''
    local ends
    for i, v in ipairs(vlist) do
        ends = i == llen and '' or ', '
        ret = ret .. tostring(v) .. ends
    end
    return ret
end

--字典转换成为更新内容
local function _crt_vallist_str(conddic)
    local str_condi = ''

    local act_val
    local idx = 1
    local condlen = table.nums(conddic)
    local exstr = ''
    for idxk, val in pairs(conddic) do
        exstr = idx ~= condlen and ', ' or ''
        act_val = tostring(val)
        str_condi = str_condi .. string.format('%s=%s', idxk, act_val) .. exstr
        idx = idx + 1
    end
    return str_condi
end


--[[ SEP ]]
local sql_limit = function(str, starnum, endnum)
    local cmd_limit = ' LIMIT {starnum}, {endnum};'
    cmd_limit = string.gsub(cmd_limit, '{starnum}', starnum)
    cmd_limit = string.gsub(cmd_limit, '{endnum}', endnum)
    return str .. cmd_limit
end

local group_by = function(str, condlist)
    local cmd_groupby = ' GROUP BY {condlist};'
    local con_group = _crt_list_condition_str(condlist)
    return str .. string.gsub(cmd_groupby, '{condlist}', con_group)
end

local LUAVTYPE2_SQLTYPE = {
    ['number'] = 'int(11)',
    ['string'] = 'varchar(20)',
    ['boolean'] = 'int(1)'
}

local CMD_FUNCMAP = {
    ['limit'] = sql_limit,
    ['group'] = group_by
}

--lua类型转换为sql值
local function _get_dblike_val(val)
    if type(val) == 'string' then
        return string.format("'%s'", val)
    elseif type(val) == 'table' then
        local ret = table.concat(val, ';')
        return string.format("'%s'", ret)
    else
        return val
    end
end

function utilrds.insert_data(tbname, ldic)
    local ret = string.gsub(TEMPLATE_INMSMERT_DATA, '{tbname}', string.format('`%s`', tbname))
    local str_defks = ''
    local str_tarvs = ''
    local act_val
    for idxk, val in pairs(ldic) do
        act_val = _get_dblike_val(val)
        str_defks = str_defks .. string.format('`%s`', idxk) .. ','
        str_tarvs = str_tarvs .. act_val .. ','
    end
    ret = string.gsub(ret, '{defienks}', string.sub(str_defks, 0, string.len(str_defks) - 1))
    ret = string.gsub(ret, '{vals}', string.sub(str_tarvs, 0, string.len(str_tarvs) - 1))
    return ret
end

function utilrds.find(tbname, condic)
    local ret = string.gsub(TEMPLATE_FIND_ONE, '{tbname}', tbname)
    local str_condi
    if #condic > 0 then
        str_condi = _crt_list_condition_str(condic)
    else
        str_condi = _crt_kv_condition_str(condic)
    end
    ret = string.gsub(ret, '{condition}', str_condi)
    return ret
end

function utilrds.find_in_set(tbname, key, vlist)
    local ret = string.gsub(TEMPLATE_FINDINSET, '{tbname}', tbname)
    local v_list = _crt_list_condition_str(vlist)
    ret = string.gsub(ret, '{condkey}', key)
    ret = string.gsub(ret, '{condlist}', v_list)
    return ret
end

function utilrds.find_in(tbname, key, vlist)
    local ret = string.gsub(TEMPLATE_FINDIN, '{tbname}', tbname)
    local v_list = _crt_list_condition_str(vlist)
    ret = string.gsub(ret, '{condkey}', key)
    ret = string.gsub(ret, '{condlist}', v_list)
    return ret
end

function utilrds.fetch_limitcnt(tbname, starnum, endnum, condic)
    local ret = string.gsub(TEMPLATE_FETCH_LIMITCNT, '{tbname}', string.format('`%s`', tbname))
    ret = string.gsub(ret, '{starnum}', tostring(starnum))
    ret = string.gsub(ret, '{endnum}', tostring(endnum))
    if condic then
        ret = ret .. _crt_kv_condition_str(condic)
    end
    return ret
end

function utilrds.dele_all(tbname, condic)
    local ret = string.gsub(TEMPLATE_DELETE_FROM, '{tbname}', tbname)
    local str_condi = _crt_kv_condition_str(condic)
    ret = string.gsub(ret, '{condition}', str_condi)
    return ret
end

function utilrds.update(tbname, valdic, condic, isformula)
    local ret = string.gsub(TEMPLATE_UPDATE_TABLE, '{tbname}', tbname)
    local str_cond = _crt_vallist_str(valdic, isformula)
    ret = string.gsub(ret, '{vallist}', str_cond)
    ret = string.gsub(ret, '{condition}', _crt_kv_condition_str(condic))
    return ret
end

function utilrds.group_by(tbname, condlist)
    local ret = string.gsub(TEMPLATE_FIND_GROUP_BY, '{tbname}', tbname)
    local cond_str = _crt_list_condition_str(condlist)
    Logger:dump( condlist, ":====>>group by cond_str%s")
    Logger:info(":====>>group by cond_str%s",  cond_str)
    return string.gsub(ret, '{condlist}', cond_str)
end

function utilrds.append_cmd(str, cmd, ...)
    -- Logger:dump({...}, "--->>dump val append")
    str = string.sub(str, 0, - 2)
    local cmd_func = CMD_FUNCMAP[cmd]
    if not cmd_func then
        Logger:warn("[utilrds]append not found the fun key[%s]", cmd)
        return
    end
    return cmd_func(str, ...)
end

return utilrds
